﻿/*
=============================================================================
Author:魏宝辉通过CodeSmithe生成
CreatedTime:2012-9-13
Description:DinnerTableList三层结构数据访问层
=============================================================================
*/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SQLite;
using FTchina.Models;//引入模型层
namespace FTchina.DAL
{
	public static partial class DinnerTableListService
	{
		#region 增加一条数据
		public static int AddDinnerTableList(DinnerTableList dinnerTableList)
		{
			string sql =
			"INSERT INTO DinnerTableList(AreaName,DiningTableName,pcount,zaoCustomerID,zaoDate,zaocount,zhongCustomerID,zhongDate,zhongcount,wanCustomerID,wanDate,wancount) "+
			"VALUES (@AreaName,@DiningTableName,@pcount,@zaoCustomerID,@zaoDate,@zaocount,@zhongCustomerID,@zhongDate,@zhongcount,@wanCustomerID,@wanDate,@wancount)";
			
			 
				SQLiteParameter[] para = new SQLiteParameter[]
				{
						
					new SQLiteParameter("@AreaName",dinnerTableList.AreaName),
						
					new SQLiteParameter("@DiningTableName",dinnerTableList.DiningTableName),
						
					new SQLiteParameter("@pcount",dinnerTableList.Pcount),
						
					new SQLiteParameter("@zaoCustomerID",dinnerTableList.ZaoCustomerID),
						
					new SQLiteParameter("@zaoDate",dinnerTableList.ZaoDate),
						
					new SQLiteParameter("@zaocount",dinnerTableList.Zaocount),
						
					new SQLiteParameter("@zhongCustomerID",dinnerTableList.ZhongCustomerID),
						
					new SQLiteParameter("@zhongDate",dinnerTableList.ZhongDate),
						
					new SQLiteParameter("@zhongcount",dinnerTableList.Zhongcount),
						
					new SQLiteParameter("@wanCustomerID",dinnerTableList.WanCustomerID),
						
					new SQLiteParameter("@wanDate",dinnerTableList.WanDate),
						
					new SQLiteParameter("@wancount",dinnerTableList.Wancount)
				};
				int newId = DBHelper.ExecuteCommand(sql, para);
				return newId;
			 
		}
		#endregion
		#region 删除数据以主键，标识列，唯一列构建的删除
			
		public static int DeleteDinnerTableListById(long id)	
		{
			string sql="DELETE From  DinnerTableList  Where id=@id";
			 SQLiteParameter[] para = new SQLiteParameter[]
				{
					new SQLiteParameter("@id", id)
				};
				return DBHelper.ExecuteCommand(sql, para);
			 
		}		
		
		#endregion
		#region 修改数据
		//考虑标识列与主键不一致情形
		//以标识列修改实体
		public static int ModifyDinnerTableListByid(DinnerTableList dinnerTableList)
		{
		string sql ="UPDATE "+"DinnerTableList"+" set "+
					"AreaName=@AreaName, "+
					"DiningTableName=@DiningTableName, "+
					"pcount=@pcount, "+
					"zaoCustomerID=@zaoCustomerID, "+
					"zaoDate=@zaoDate, "+
					"zaocount=@zaocount, "+
					"zhongCustomerID=@zhongCustomerID, "+
					"zhongDate=@zhongDate, "+
					"zhongcount=@zhongcount, "+
					"wanCustomerID=@wanCustomerID, "+
					"wanDate=@wanDate, "+
					"wancount=@wancount "+ 
				" Where id=@id";
			 
				SQLiteParameter[] para = new SQLiteParameter[]
				{
					new SQLiteParameter("@id",dinnerTableList.Id),
					new SQLiteParameter("@AreaName",dinnerTableList.AreaName),
					new SQLiteParameter("@DiningTableName",dinnerTableList.DiningTableName),
					new SQLiteParameter("@pcount",dinnerTableList.Pcount),
					new SQLiteParameter("@zaoCustomerID",dinnerTableList.ZaoCustomerID),
					new SQLiteParameter("@zaoDate",dinnerTableList.ZaoDate),
					new SQLiteParameter("@zaocount",dinnerTableList.Zaocount),
					new SQLiteParameter("@zhongCustomerID",dinnerTableList.ZhongCustomerID),
					new SQLiteParameter("@zhongDate",dinnerTableList.ZhongDate),
					new SQLiteParameter("@zhongcount",dinnerTableList.Zhongcount),
					new SQLiteParameter("@wanCustomerID",dinnerTableList.WanCustomerID),
					new SQLiteParameter("@wanDate",dinnerTableList.WanDate),
					new SQLiteParameter("@wancount",dinnerTableList.Wancount)
				};
				return DBHelper.ExecuteCommand(sql, para);
			 
		}					
		#endregion
		#region 查询数据
		public static IList<DinnerTableList> GetAllDinnerTableLists()
		{
			string sqlAll = "SELECT * FROM DinnerTableList";
			return GetDinnerTableListsBySql(sqlAll);
		}
		//以标识列获得对象
		public static DinnerTableList GetDinnerTableListByid(object id)
		{
			string sql = "SELECT * FROM "+"DinnerTableList"+" WHERE id = @id";
			
            using (SQLiteDataReader reader = DBHelper.GetReader(sql, new SQLiteParameter("@id", id)))
			{
				 
				if (reader.Read())
				{
					DinnerTableList dinnerTableList = new DinnerTableList();
					dinnerTableList.Id=Convert.ToInt32(reader["id"]);
					dinnerTableList.AreaName=Convert.ToString(reader["AreaName"]);
					dinnerTableList.DiningTableName=Convert.ToString(reader["DiningTableName"]);
					dinnerTableList.Pcount=Convert.ToInt32(reader["pcount"]);
					dinnerTableList.ZaoCustomerID=Convert.ToString(reader["zaoCustomerID"]);
					dinnerTableList.ZaoDate=Convert.ToDateTime(reader["zaoDate"]);
					dinnerTableList.Zaocount=Convert.ToString(reader["zaocount"]);
					dinnerTableList.ZhongCustomerID=Convert.ToString(reader["zhongCustomerID"]);
					dinnerTableList.ZhongDate=Convert.ToDateTime(reader["zhongDate"]);
					dinnerTableList.Zhongcount=Convert.ToString(reader["zhongcount"]);
					dinnerTableList.WanCustomerID=Convert.ToString(reader["wanCustomerID"]);
					dinnerTableList.WanDate=Convert.ToDateTime(reader["wanDate"]);
					dinnerTableList.Wancount=Convert.ToString(reader["wancount"]);
					reader.Close();
					
					return dinnerTableList;
					}
				else
				{
					reader.Close();
					return null;
				}
			}
			 
		}
					
		//私有方法供构建好的SQL语句调用
		private static IList<DinnerTableList> GetDinnerTableListsBySql(string safeSql)
		{
			List<DinnerTableList> list = new List<DinnerTableList>();
			 
            using (DataTable table = DBHelper.GetDataTable(safeSql))
			{
				foreach (DataRow row in table.Rows)
				{
					DinnerTableList dinnerTableList = new DinnerTableList();
					dinnerTableList.Id=Convert.ToInt32(row["id"]);
					dinnerTableList.AreaName=Convert.ToString(row["AreaName"]);
					dinnerTableList.DiningTableName=Convert.ToString(row["DiningTableName"]);
					dinnerTableList.Pcount=Convert.ToInt32(row["pcount"]);
					dinnerTableList.ZaoCustomerID=Convert.ToString(row["zaoCustomerID"]);
					dinnerTableList.ZaoDate=Convert.ToDateTime(row["zaoDate"]);
					dinnerTableList.Zaocount=Convert.ToString(row["zaocount"]);
					dinnerTableList.ZhongCustomerID=Convert.ToString(row["zhongCustomerID"]);
					dinnerTableList.ZhongDate=Convert.ToDateTime(row["zhongDate"]);
					dinnerTableList.Zhongcount=Convert.ToString(row["zhongcount"]);
					dinnerTableList.WanCustomerID=Convert.ToString(row["wanCustomerID"]);
					dinnerTableList.WanDate=Convert.ToDateTime(row["wanDate"]);
					dinnerTableList.Wancount=Convert.ToString(row["wancount"]);
					list.Add(dinnerTableList);
				}
				return list;
			}
             
		}
		private static IList<DinnerTableList> GetDinnerTableListsBySql( string sql, params SQLiteParameter[] values )
		{
			List<DinnerTableList> list = new List<DinnerTableList>();
			 
            using (DataTable table = DBHelper.GetDataTable(sql, values))
			{
				foreach (DataRow row in table.Rows)
				{
					DinnerTableList dinnerTableList = new DinnerTableList();
					dinnerTableList.Id=Convert.ToInt32(row["id"]);
					dinnerTableList.AreaName=Convert.ToString(row["AreaName"]);
					dinnerTableList.DiningTableName=Convert.ToString(row["DiningTableName"]);
					dinnerTableList.Pcount=Convert.ToInt32(row["pcount"]);
					dinnerTableList.ZaoCustomerID=Convert.ToString(row["zaoCustomerID"]);
					dinnerTableList.ZaoDate=Convert.ToDateTime(row["zaoDate"]);
					dinnerTableList.Zaocount=Convert.ToString(row["zaocount"]);
					dinnerTableList.ZhongCustomerID=Convert.ToString(row["zhongCustomerID"]);
					dinnerTableList.ZhongDate=Convert.ToDateTime(row["zhongDate"]);
					dinnerTableList.Zhongcount=Convert.ToString(row["zhongcount"]);
					dinnerTableList.WanCustomerID=Convert.ToString(row["wanCustomerID"]);
					dinnerTableList.WanDate=Convert.ToDateTime(row["wanDate"]);
					dinnerTableList.Wancount=Convert.ToString(row["wancount"]);
					list.Add(dinnerTableList);
				}
				return list;
			}
            
			 
		}
		#endregion
		 
	}
}

